package com.verisette.ncos.persistence.dao;

import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;
import org.corp.agi.common.bean.PagingBean;
import org.corp.agi.persistence.dao.CommonDAOImpl;
import org.corp.agi.service.bean.UserBean;

import com.verisette.ncos.persistence.domain.CustomMasterObjectAuthByUsg;
import com.verisette.ncos.persistence.domain.MasterObject;
import com.verisette.ncos.persistence.domain.MasterUserGroup;

/**
 * {Description here}
 *
 * @author Thippawan Muangchu (Tip)
 * @since Mar 31, 2010 4:15:41 PM
 * @version 1.0.0
 * @see 
 */
public class UserGroupAuthorizationDAOImpl extends CommonDAOImpl implements UserGroupAuthorizationDAO {
	private Logger logger = Logger.getLogger(UserGroupAuthorizationDAOImpl.class);
	
	//load user group
	public List <MasterUserGroup> loadUserGroup(){
		StringBuffer sb = new StringBuffer();
		HashMap<String, String> userGroupMap = new HashMap<String, String>();
		sb.append("Select * From MASTER_USER_GROUP WHERE (USG_STATUS = 'A') ORDER BY USG_NAME");
		List <MasterUserGroup> list = (List<MasterUserGroup>) nativeQuery(sb.toString(), MasterUserGroup.class);
		return list;
	}
	
	//getAuthListByGroupTotal() 
	public int getAuthListByGroupTotal(MasterUserGroup userGroupBean){
		StringBuffer sb = new StringBuffer();
		List param = new LinkedList();
		sb.append("select count(*)");
		sb.append(" from master_object_auth_by_usg");
		sb.append(" where usg_id = ?");

		//set param value
		param.add(userGroupBean.getUsgId());
		
		Integer total = (Integer) nativeQuerySingleResult(sb.toString(), param.toArray());
		return total.intValue();
	}
	
	//get auth list by group
	public List <CustomMasterObjectAuthByUsg> getAuthListByGroup(MasterUserGroup userGroupBean, PagingBean paging){
		StringBuffer sb = new StringBuffer();
		List param = new LinkedList();
		sb.append("SELECT MASTER_OBJECT.OBJ_ID, MASTER_OBJECT.OBJ_NAME, MASTER_OBJECT.OBJ_LVL, MASTER_OBJECT_AUTH_BY_USG.OAG_ID, MASTER_OBJECT_AUTH_BY_USG.OAG_ENABLE, MASTER_OBJECT_AUTH_BY_USG.USG_ID");
		sb.append(" FROM MASTER_OBJECT_AUTH_BY_USG");
		sb.append(" INNER JOIN MASTER_OBJECT");
		sb.append(" ON MASTER_OBJECT_AUTH_BY_USG.OBJ_ID = MASTER_OBJECT.OBJ_ID");
		sb.append(" WHERE (MASTER_OBJECT.OBJ_STATUS = 'A')");
		sb.append(" AND (MASTER_OBJECT_AUTH_BY_USG.USG_ID = ?)");
		//sb.append(" ORDER BY MASTER_OBJECT.OBJ_ID ");
		//set param value
		logger.debug("param :" + userGroupBean.getUsgId()+","+paging);
		param.add(userGroupBean.getUsgId());

		//List <CustomMasterObjectAuthByUsg> list = (List<CustomMasterObjectAuthByUsg>) nativeQuery(sb.toString(), CustomMasterObjectAuthByUsg.class, paging, param.toArray());
		
		// Remove Paging 20100525@Ratapong
		List <CustomMasterObjectAuthByUsg> list = (List<CustomMasterObjectAuthByUsg>) nativeQuery(sb.toString(), CustomMasterObjectAuthByUsg.class, param.toArray());
		return list;
	}
	
	//get object list all
	public List <MasterObject> getObjListAll(PagingBean paging){
		StringBuffer sb = new StringBuffer();
		sb.append("select *");
		sb.append(" from master_object");
		sb.append(" where obj_status ='A' order by obj_id, obj_lvl");	
		//List <MasterObject> list = (List<MasterObject>) nativeQuery(sb.toString(), MasterObject.class, paging);
		
		// Remove Paging 20100525@Ratapong
		List <MasterObject> list = (List<MasterObject>) nativeQuery(sb.toString(), MasterObject.class);
		
		return list;
	}

	@Override
	public void updateUserGroup(CustomMasterObjectAuthByUsg customMasterObjectAuthByUsg, UserBean user) {
		StringBuffer sb = new StringBuffer();
		List param = new LinkedList();
		sb.append("Update MASTER_OBJECT_AUTH_BY_USG");
		sb.append(" Set OAG_ENABLE = ?,");
		sb.append(" OAG_LAST_UPDATED_DATE = GETDATE(),");
		sb.append(" OAG_LAST_UPDATED_BY = ?");
		sb.append(" Where OBJ_ID = ?");	
		sb.append(" And USG_ID = ?");	
		
		logger.debug("param ==>" + user.getUsername() + "," + customMasterObjectAuthByUsg.getOagEnable() + "," + customMasterObjectAuthByUsg.getObjId() + "," + customMasterObjectAuthByUsg.getUsgId());
		param.add(customMasterObjectAuthByUsg.getOagEnable());
		param.add(user.getUsername());
		param.add(customMasterObjectAuthByUsg.getObjId());
		param.add(customMasterObjectAuthByUsg.getUsgId());
		
		executeNativeSQL(sb.toString(),  param.toArray());
		
	}

	@Override
	public void insertUserGroup(CustomMasterObjectAuthByUsg customMasterObjectAuthByUsg, UserBean user) {
		StringBuffer sb = new StringBuffer();
		List param = new LinkedList();
		sb.append("INSERT INTO MASTER_OBJECT_AUTH_BY_USG");
		sb.append(" (OAG_ID, OBJ_ID, USG_ID, OAG_ENABLE, OAG_CREATED_DATE, OAG_CREATED_BY)");
		sb.append(" VALUES (?, ?, ?, ?, GETDATE(), ?)");
		
		
		logger.debug("param ==>" + user.getUsername() + "," + customMasterObjectAuthByUsg.getOagId() + "," + customMasterObjectAuthByUsg.getObjId() + "," + customMasterObjectAuthByUsg.getUsgId() + "," + customMasterObjectAuthByUsg.getOagEnable());
		param.add(customMasterObjectAuthByUsg.getOagId());
		param.add(customMasterObjectAuthByUsg.getObjId());
		param.add(customMasterObjectAuthByUsg.getUsgId());
		param.add(customMasterObjectAuthByUsg.getOagEnable());
		param.add(user.getUsername());

		executeNativeSQL(sb.toString(),  param.toArray());
	}

	@Override
	public int findMaxOagId() {
		StringBuffer sb = new StringBuffer();
		sb.append("Select Max(OAG_ID) From MASTER_OBJECT_AUTH_BY_USG");

		int maxOagId = ((Short)nativeQuerySingleResult(sb.toString())).intValue();
		logger.debug("maxOagId:" + maxOagId);
		return maxOagId;
	}
	
}
